/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import java.io.IOException;
import java.io.PrintWriter;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author Bor
 */
@WebServlet(name = "sel1", urlPatterns = {"/sel1"})
public class sel1 extends HttpServlet {

    String connectionURL = "jdbc:postgresql://192.168.1.89:5432/postgis";
    Connection connection = null;
    String username = "pgsql";
    String pass = "pgsql";

    
    

    /**
     * Processes requests for both HTTP
     * <code>GET</code> and
     * <code>POST</code> methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        String param1 = "";

        try {
            connectionURL = getServletConfig().getInitParameter("pgcon");
            username = getServletConfig().getInitParameter("pgusr");
            pass = getServletConfig().getInitParameter("pgpwd");
        } finally {
        }
        
        try {
            param1 = new String(request.getParameter("param1").getBytes("ISO-8859-1"), "utf-8");
            //param1 = request.getParameter("param1");
            //param1 = URLEncoder.encode(request.getParameter("param1"),"UTF-8");
            //param1 = URLDecoder.decode(new String(request.getParameter("param1").getBytes("ISO-8859-1"), "utf-8"));
        }catch(Exception e){
        }
        finally {
        }
        
        String sqlreq="select * from t_first where sdescr ilike '%" + param1 +"%' order by id desc ;";
        try {
            
            /* TODO output your page here. You may use following sample code. */
            out.println(selectData(sqlreq, 5));//insert Data
//            out.println("<html>");
//            out.println("<head>");
//            out.println("<title>Servlet sel</title>");
//            out.println("</head>");
//            out.println("<body>");
//            out.println("<h1>Servlet sel at " + request.getContextPath() + "</h1>");
            //out.println("URLEncoder.encode:"+URLEncoder.encode(sqlreq)+"</br>");
            //out.println("URLDecoder.decode:"+URLDecoder.decode(sqlreq)+"</br>");
            //out.println("sql:"+sqlreq+"</br>");
            //String s = new String(request.getQueryString().getBytes("ISO-8859-1"), "utf-8");
            //out.println("request.getQueryString().getBytes(\"ISO-8859-1\"), \"utf-8\":"+s+"</br>");
            
//            out.println("</html>");
        } finally {
            out.close();
        }

    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP
     * <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP
     * <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

    public String selectData(String selstring, int colcount) {
        String result = " string resilt - inited ";

        try {
// Load the database driver
            Class.forName("org.postgresql.Driver");
// Get a Connection to the database
            connection = DriverManager.getConnection(connectionURL, username, pass);
//Add the data into the database
//String sql = "insert into sample values(?,?)";

            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery(selstring);

//get request     
            //SELECT id, sdescr, descr, createtime, moditime FROM t_first;


//------TO TABLE            
//            result = "<table >";
//            while (rs.next()) {
////                result += "<br/>";
//                result += "<tr>";
//                for (int i = 1; i < colcount + 1; i++) {
//                    result += "<td>" + rs.getString(i) + "</td>";
//                }
//                result += "</tr>";
//            }
//            result += "</table>";
//            result += "<hr/>";
//\------TO TABLE            

//------TO JSON            
            String isFirstRow = ""; //isFirst "" else ","
            result = "{ \"items\":[";
            while (rs.next()) {
                result += isFirstRow + "{";
                isFirstRow = ",";
                String isFirstCol = "\n"; //isFirstCol "" else "/n,"
                for (int i = 1; i < colcount + 1; i++) {
                    result += isFirstCol + " \"col" + i + "\":\"" + rs.getString(i) + "\"";
                    isFirstCol = ",";
                }
                result += "}";
            }
            result += "]}";
            //json sample http://dojotoolkit.org/documentation/tutorials/1.8/populating_datagrid/demo/hof-batting.json
//\------TO JSON            

//\get request            


            rs.close();
            st.close();
        } catch (ClassNotFoundException e) {
            result = "Couldn't load database driver: " + e.getMessage();
        } catch (SQLException e) {
            result = "SQLException caught: " + e.getMessage();
        } catch (Exception e) {
            result = e + ".";
        } finally {
// Always close the database connection.
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException ignored) {
                result = ignored + ".";
            }
        }
        return result;// + ":" + connectionURL + "." + username + "." + pass;
    }
}